﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.mPPC.Common
{
    public class GetDataTable
    {
        /// <summary>
        /// 获取所有未出CSV订单
        /// </summary>
        /// <returns></returns>
        public static DataTable GetTable_CSV()
        {
            DataTable dataTable = new DataTable();
            string sql = @"SELECT distinct a.PRODUCT_DATE,a.PRODUCT_ORDER_NO,a.FOLLOW_NO
                                FROM Transfer_Panel_Cutting AS a
                                LEFT JOIN PPC_HPL430_Cutting_State AS b
                                ON a.PRODUCT_ORDER_NO=b.PRODUCT_ORDER_NO and a.FOLLOW_NO=b.FOLLOW_NO
                                WHERE ISNULL(doneCSV,'')='' AND UPPER(ISNULL(a.FOLLOW_NO,'')) LIKE 'PN%' ORDER BY a.FOLLOW_NO,a.PRODUCT_ORDER_NO";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        /// <summary>
        /// 获取待转CSV明细列表
        /// </summary>
        /// <param name="colum_where">查询条件</param>
        /// <returns></returns>
        public static DataTable GetTable_Detail(string colum_where)
        {
            DataTable dataTable = new DataTable();
            string sql = @"SELECT PRODUCT_DATE as 生产日期,a.PRODUCT_ORDER_NO as 生产订单,PRODUCT_ORDER_QTY as 订单数量,
                            a.FOLLOW_NO as 跟进表号, a.PART_NO as 部件编号, a.PART_DESC as 部件描述,a.THICKNESS as 厚度,
                            a.CUTTING_TYPE as 开料类型,	a.SNG_CUT_SIZE_X as 单开_X,	a.SNG_CUT_SIZE_Y as 单开_Y,
                            a.DBL_CUT_SIZE_X as 双开_X,	a.DBL_CUT_SIZE_Y as 双开_Y,a.CUTTING_QTY as 开料数量,a.TRANSFER_BATCH_NO as 传输批次,
                            a.CUTTING_SERIAL_NO as 切割序列号,a.MATERIAL_NO as 材料编号,a.MES_DECOR
                            FROM Transfer_Panel_Cutting AS a
                            LEFT JOIN PPC_HPL430_Cutting_State AS b
                            ON a.PRODUCT_ORDER_NO=b.PRODUCT_ORDER_NO and a.FOLLOW_NO=b.FOLLOW_NO
                            WHERE 1=1 and " + colum_where + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        /// <summary>
        /// 获取PTX csv 明细-中文
        /// </summary>
        /// <param name="colum_where"></param>
        /// <returns></returns>
        public static DataTable GetTable_Detail_PTX(string colum_where)
        {
            DataTable dataTable = new DataTable();
            string sql = @"SELECT [BARCODE]
              ,[INFO_20] as '文件名'
              ,[MATERIAL]
              ,[LENGTH]
              ,[WIDTH]
              ,[THICKNESS]
              ,[GRAIN]
              ,[QUANTITY]
              ,[TYPE]
              ,[Finish_Length]
              ,[Finish_Width]
              ,[PART_TYPE]
              ,[PART_NO]
              ,[PART_DESC]
              ,[MODEL_NO]
              ,[PRODUCT_DATE]
              ,[CUTTING_TYPE]
              ,[MACHINE_SAW]
              ,[GRID_POSITION]
              ,[FINISH_DATE]
              ,[FACE_COLOR_A]
              ,[FACE_COLOR_B]
              ,[FOLLOW_NO]
              ,[DWG_NO]
              ,[PRODUCT_ORDER_NO]
              ,[TRANSFER_BATCH_NO]
              ,[ORDER_QTY]
              ,[PRIORITY]           
              FROM [LamexMESManager].[dbo].[PPC_HPL430_CSV]
              WHERE 1=1 and " + colum_where + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        /// <summary>
        /// 获取PTX csv 明细-重生成时用-英文
        /// </summary>
        /// <param name="colum_where"></param>
        /// <returns></returns>
        public static DataTable GetTable_Detail_PTX_EN(string colum_where)
        {
            DataTable dataTable = new DataTable();
            string sql = @"SELECT [BARCODE]
              ,[MATERIAL]
              ,[LENGTH]
              ,[WIDTH]
              ,[THICKNESS]
              ,[GRAIN]
              ,[QUANTITY]
              ,[TYPE]
              ,[Finish_Length]
              ,[Finish_Width]
              ,[PART_TYPE]
              ,[PART_NO]
              ,[PART_DESC]
              ,[MODEL_NO]
              ,[PRODUCT_DATE]
              ,[CUTTING_TYPE]
              ,[MACHINE_SAW]
              ,[GRID_POSITION]
              ,[FINISH_DATE]
              ,[FACE_COLOR_A]
              ,[FACE_COLOR_B]
              ,[FOLLOW_NO]
              ,[DWG_NO]
              ,[PRODUCT_ORDER_NO]
              ,[TRANSFER_BATCH_NO]
              ,[ORDER_QTY]
              ,[PRIORITY]
              ,[INFO_20]
              FROM [LamexMESManager].[dbo].[PPC_HPL430_CSV]
              WHERE 1=1 and " + colum_where + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        /// <summary>
        /// 获取SAP传过来的数据-导出csv时数据源
        /// </summary>
        /// <param name="follow_no"></param>
        /// <returns></returns>
        public static DataTable GetCSV_Detail(string follow_no,string size)
        {
            DataTable dataTable = new DataTable();
            string sql = @"select * from [LamexMESManager].[dbo].[Transfer_Panel_Cutting] 
            where 1=1 AND CUTTING_TYPE='正常开料' 
            AND UPPER(ISNULL(MATERIAL_TYPE,''))!='EVA' AND UPPER(ISNULL(FOLLOW_NO,'')) LIKE 'PN%' AND SNG_CUT_SIZE_X>=" + size + " AND SNG_CUT_SIZE_Y>="+ size + " " + follow_no + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        /// <summary>
        /// 获取流水号
        /// </summary>
        /// <returns></returns>
        public static string GetSerialNo()
        {
            DataTable dataTable = new DataTable();
            string stringNo = "";
            string sql = @"SELECT top 1 BARCODE  FROM [LamexMESManager].[dbo].[PPC_HPL430_CSV]
            order by BARCODE desc";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            if (dataTable.Rows.Count > 0)
                stringNo = dataTable.Rows[0][0].ToString();
            return stringNo;
        }
        /// <summary>
        /// 获取[PPC_HPL430_Cutting_State]表只存在csv时间的Optimization_No优化单号
        /// </summary>
        /// <returns></returns>
        public static DataTable GetOpt_State()
        {
            DataTable dataTable = new DataTable();
            string sql = @" select DISTINCT Optimization_No FROM [LamexMESManager].[dbo].[PPC_HPL430_Cutting_State]
            WHERE ISNULL(doneCSV,'')!='' AND ISNULL(donePTX,'')='' 
            AND ISNULL(doneTransfer,'')='' AND ISNULL(doneCutting,'')=''";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        public static DataTable GetMES_Decor(List<string> strORDERNO, List<string> strFOLLOWNO)
        {
            DataTable dataTable = new DataTable();
            StringBuilder s3 = new StringBuilder();
            StringBuilder s4 = new StringBuilder();
            s3.AppendFormat(@" AND PRODUCT_ORDER_NO  IN ('{0}')", string.Join("','", strORDERNO.ToArray()));
            s4.AppendFormat(@" AND FOLLOW_NO  IN ('{0}')", string.Join("','", strFOLLOWNO.ToArray()));
            string sql = @" SELECT MES_DECOR FROM [LamexMESManager].[dbo].[Transfer_Panel_Cutting] WHERE 1=1 " + s3 + "" + s4 + " ";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        //根据流水号得到厚度
        public static DataTable GetThick(string barCode)
        {
            DataTable dataTable = new DataTable();
            string sql = @" SELECT THICKNESS FROM [LamexMESManager].[dbo].[PPC_HPL430_CSV] 
            WHERE BARCODE='" + barCode + "'";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
    }
}
